﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Web;
using Pub;
using System.Windows.Forms;
using System.IO;
using System.Drawing;
using System.Drawing.Imaging;

namespace JCNT.DAL
{
    public class CSQLite : IADO
    {
        public static String S_Con;
        public SQLiteConnection cn;
        public SQLiteDataAdapter da;
        public SQLiteCommand cm;
        public DataSet ds;
        public int ds_count = 0;
        public SQLiteParameter[] pars;

        public CSQLite()
        {
            OpenDB();
        }

        public Boolean OpenDB()
        {
            S_Con = CConfig.getPermite("DBCon");
            //S_Con = CConfig.getConStr();
            //CPub.Send(S_Con);
            cn = new SQLiteConnection(S_Con);
            try
            {
                cn.Open();
                return true;
            }
            catch (Exception ex)
            {
                CPub.Send("CSQLServer.cs OpenDB:" + ex.ToString());
                return false;
            }

        }

        public Boolean CloseDB()
        {
            try
            {
                ds = null;
                da = null;
                return true;
            }
            catch (Exception ex)
            {
                CPub.Send("CSQLServer.cs CloseDB:" + ex.ToString());
                return false;
            }
        }

        public DataSet sel1(string sql)
        {
            //conText = ConfigurationManager.ConnectionStrings["DBCONN"].ToString();
            //con = new SqlConnection(conText);
            da = new SQLiteDataAdapter(sql, cn);
            ds = new DataSet();
            try
            {
                da.Fill(ds, "table");
                ds_count = ds.Tables[0].Rows.Count;
            }
            catch (Exception ex)
            {
                ds = null;
                ds_count = 0;
                CPub.Send(sql);
                CPub.Send(ex.ToString());
            }
            return ds;
        }

        /// <summary>
        /// 执行非select语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd1(String sql)
        {
            try
            {
                cm = new SQLiteCommand(sql, cn);
                return cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                CPub.Send("CSQLServer.cs cmd1:" + ex.ToString());
                return 0;
            }

        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int cmd2(String sql)
        {
            if (pars == null) return 0;

            cm.Connection = cn;
            cm.CommandText = sql;
            cm.Parameters.Clear();
            cm.Parameters.AddRange(pars);
            cm.CommandType = CommandType.StoredProcedure;
            return cm.ExecuteNonQuery();
        }

        public void setParsCount(int i_count)
        {
            pars = new SQLiteParameter[i_count];
            for (int i = 0; i < i_count; i++)
            {
                pars[i] = new SQLiteParameter();
            }
        }

        public string dr(string sCol)
        {
            if (ds_count > 0)
                return ds.Tables[0].Rows[0][sCol].ToString();
            else
                return "";
        }

        public int getNewID(string Tab, string Col)
        {

            ds = sel1("select Isnull(Max(Convert(int," + Col + ")),0) + 1 as s1 from " + Tab);

            if (ds_count == 1)
                return Convert.ToInt32(dr("s1"));
            else
                return 0;
        }

        public Image drImg(string sCol)
        {
            if (ds_count > 0)
            {
                try
                {
                    MemoryStream ms = new MemoryStream((byte[])ds.Tables[0].Rows[0][sCol]);
                    ms.Position = 0;
                    Image img = Image.FromStream(ms);
                    //ms.Close();
                    return img;
                }
                catch (System.Exception ex)
                {
                    return null;
                }

            }
            else
                return null;
        }

        public void save_RchText(string tab, string par1, string col, RichTextBox tbox1)
        {

            MemoryStream stream = new MemoryStream();
            tbox1.SaveFile(stream, RichTextBoxStreamType.RichText);

            Byte[] bytes = stream.ToArray();
            stream.Close();
            String sql = "update " + tab + " set " + col + " = @bytes " + par1;
            cm = new SQLiteCommand(sql, cn);
            cm.Parameters.Add("@bytes", DbType.Binary).Value = bytes;
            cm.ExecuteNonQuery();
        }

        public void save_img(string tab, string par1, string col, Image img)
        {
            MemoryStream stream = new MemoryStream();
            img.Save(stream, ImageFormat.Png);
            Byte[] bytes = stream.ToArray();
            String sql = "update " + tab + " set " + col + " = @bytes " + par1;
            cm = new SQLiteCommand(sql, cn);
            cm.Parameters.Add("@bytes", DbType.Binary).Value = bytes;
            cm.ExecuteNonQuery();
        }

        public Image read_img(string sql, string col)
        {
            //string sql = "select * from " + tab + " where id=" + id;   
            DataSet ds = sel1(sql);
            if (ds_count == 0)
                return null;
            try
            {
                Byte[] Bytes = (Byte[])ds.Tables[0].Rows[0][col];
                MemoryStream mStream = new MemoryStream();
                mStream.Write(Bytes, 0, Bytes.Length);
                Image Img = Image.FromStream(mStream);
                //mStream.Close();
                return Img;
            }
            catch (System.Exception ex)
            {
                CPub.Send("CSQLServer.cs read_img:" + ex.ToString());
                return null;
            }

        }

        public Boolean update()
        {
            try
            {
                da.Update(ds);
                return true;
            }
            catch (System.Exception ex)
            {
                CPub.Send(ex.ToString());
                CPub.Send("更新数据是发生错误", ex);
                return false;
            }
        }

    }
}
